#! /bin/bash
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e "

-- 数据公共层 CDM

-- DWD明细层，明细粒度事实层
-- 数据粒度和ODS层一致，主要对数据进行清洗转换等操作。

create database if not exists edu_dwd;
use edu_dwd;
/*
--  数据来源 edu_ods
-- 列的分隔符：\t
-- 表的类型：分区表、分桶表
-- customer_relationship 分区、分桶表，
	PARTITIONED BY(start_time STRING)
	clustered by(id) sorted by(id) into 10 buckets
--  customer_clue 分区、分桶表，
	PARTITIONED BY(starts_time STRING)
    clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
-- 文件类型：orc
-- 是否压缩：SNAPPY
-- 同步方式：全量同步
*/

-- 需求：报名分析主题
-- 细分：报名人数分析、意向量转报名分析、有效线索量转报名分析


-- 建表语句
-- 报名人数分析，
-- customer_relationship表、维度表
-- 按照报名的年月日来进行分区，按照支付状态：支付状态为PAID来进行统计
DROP TABLE edu_dwd.customer_signup_dwd;
CREATE TABLE IF NOT EXISTS edu_dwd.customer_signup_dwd (
    `id` int COMMENT '客户关系id',
    `customer_id` int COMMENT '所属客户id',
    `origin_type` STRING COMMENT '数据来源',
    `payment_time` STRING COMMENT '支付状态变动时间字符串',
    `payment_time_hour` STRING COMMENT '支付状态变动小时',
    `itcast_clazz_id` int COMMENT '报名课程id',
    `creator` int COMMENT '创建人id',
    `origin_type_state` STRING COMMENT '数据来源:0.线下；1.线上' )
comment '客户报名表'
PARTITIONED BY (payment_time_year String, payment_time_month String, payment_time_day String)
CLUSTERED BY(id) sorted by(id) into 10 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='itcast_clazz_id,creator');

-- 意向转报名分析，
-- customer_relationship表、维度表
-- 按照客户意向表创建时间来进行分区
drop table edu_dwd.`customer_intention_dwd`;
CREATE TABLE IF NOT EXISTS edu_dwd.`customer_intention_dwd`(
    `rid` int COMMENT 'id',
    `customer_id` STRING COMMENT '客户id',
    `create_date_time` STRING COMMENT '创建时间',
    `deleted` STRING COMMENT '是否被删除',
    `origin_type` STRING COMMENT '来源渠道',
    `itcast_school_id` STRING COMMENT '校区id',
    `itcast_subject_id` STRING COMMENT '学科id',
    `creator` int COMMENT '创建人',
    `hourinfo` STRING COMMENT '小时信息',
    `origin_type_state` STRING COMMENT '数据来源:0.线下；1.线上'
)
comment '客户意向表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(rid) sorted by(rid) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');


-- 有效线索转报名分析，
-- customer_relationship表、customer_clue表、customer_appeal表

DROP TABLE edu_dwd.customer_clue_dwd;
CREATE TABLE IF NOT EXISTS edu_dwd.customer_clue_dwd (
    `id` STRING COMMENT '线索id',
    `customer_relationship_id` int COMMENT '客户关系id',
    `origin_type_stat` STRING COMMENT '数据来源:0.线下；1.线上',
    `for_new_user` STRING COMMENT '0:未知；1：新客户线索；2：旧客户线索',
    `deleted` STRING COMMENT '是否删除',
    `create_date_time` BIGINT COMMENT '创建时间',
    `hourinfo` STRING COMMENT '小时信息'
)
comment '客户申诉dwd表'
PARTITIONED BY(yearinfo STRING,monthinfo STRING,dayinfo STRING)
clustered by(customer_relationship_id) sorted by(customer_relationship_id) into 10 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.create.index'='true','orc.bloom.filter.columns'='customer_relationship_id');
"